﻿CREATE FUNCTION func_getDonViTinhMacDinh
(
	@MaVatTu nvarchar(10)
)
RETURNS nvarchar(20)
AS
BEGIN
	-- Declare the return variable here
	Declare @DonViTinh nvarchar(20)
	
	select @DonViTinh = DonViTinh from DonViTinh where MaVatTu = @MaVatTu and LaMacDinh = 1
	
	return @DonViTinh
END
go

CREATE FUNCTION [dbo].[func_ChuyenCoDauThanhKhongDau](@inputVar NVARCHAR(MAX) )
RETURNS NVARCHAR(MAX)
AS
BEGIN 
IF (@inputVar IS NULL OR @inputVar = '') RETURN ''

DECLARE @RT NVARCHAR(MAX)
DECLARE @SIGN_CHARS NCHAR(256)
DECLARE @UNSIGN_CHARS NCHAR (256)

SET @SIGN_CHARS = N'ăâđêôơưàảãạáằẳẵặắầẩ� ��ậấèẻẽẹéềểễệếìỉĩịíòỏõọóồổỗộốờởỡợớùủũụúừửữựứỳỷỹỵýĂÂĐÊÔƠƯÀẢÃẠÁẰẲẴẶẮẦẨẪẬẤÈẺẼẸÉỀỂỄỆẾÌỈĨỊÍÒỎÕỌÓỒỔỖ���ỐỜỞỠỢỚÙỦŨỤÚỪỬỮỰỨỲ���ỸỴÝ' + NCHAR(272) + NCHAR(208)
SET @UNSIGN_CHARS = N'aadeoouaaaaaaaaaaaaa aaaaeeeeeeeeeeiiiiiooooooooooooooouuuuuuuuuuyyyyyAADEOOUAAAAAAAAAAAAAAAEEEE EEEEEEIIIIIOOOOOOOOOOOOOOOOOUUUUUUUUUUYYYYYYYDD'

DECLARE @COUNTER int
DECLARE @COUNTER1 int

SET @COUNTER = 1
WHILE (@COUNTER <= LEN(@inputVar))
BEGIN 
SET @COUNTER1 = 1
WHILE (@COUNTER1 <= LEN(@SIGN_CHARS) + 1)
BEGIN
IF UNICODE(SUBSTRING(@SIGN_CHARS, @COUNTER1,1)) = UNICODE(SUBSTRING(@inputVar,@COUNTER ,1))
BEGIN 
IF @COUNTER = 1
SET @inputVar = SUBSTRING(@UNSIGN_CHARS, @COUNTER1,1) + SUBSTRING(@inputVar, @COUNTER+1,LEN(@inputVar)-1) 
ELSE
SET @inputVar = SUBSTRING(@inputVar, 1, @COUNTER-1) +SUBSTRING(@UNSIGN_CHARS, @COUNTER1,1) + SUBSTRING(@inputVar, @COUNTER+1,LEN(@inputVar)- @COUNTER)
BREAK
END
SET @COUNTER1 = @COUNTER1 +1
END
SET @COUNTER = @COUNTER +1
END
-- SET @inputVar = replace(@inputVar,' ','-')
RETURN @inputVar
END
Go

CREATE FUNCTION func_getTenVatTuFromMaVatTu
(
 @MaVatTu varchar(10)
)
RETURNS nvarchar(100)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @TenVatTu nvarchar(100)

	-- Add the T-SQL statements to compute the return value here
	SELECT @TenVatTu = TenVatTu from VatTuHangHoa where MaVatTu = @MaVatTu

	-- Return the result of the function
	RETURN @TenVatTu

END
GO

CREATE FUNCTION func_getSoLuongTonKho
(
	@MaVatTu varchar(10)
)
RETURNS nvarchar(max)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @SoLuong float,
			@SoLuongTonKho nvarchar(max),
			@curDanhSachDonViTinh Cursor,
			@DonViTinh nvarchar(50),
			@GiaTriQuiDoi float,
			@SoLuongQuiDoi int

	-- Add the T-SQL statements to compute the return value here
	set @SoLuongTonKho = ''
	SELECT @Soluong = SoLuong from HangHoaTrongKho where MaVatTu = @MaVatTu
	Set @curDanhSachDonViTinh = cursor for select DonViTinh, GiaTriQuiDoi from DonViTinh where MaVatTu = @MaVatTu order by GiaTriQuiDoi desc
	
	OPEN @curDanhSachDonViTinh
	FETCH NEXT FROM @curDanhSachDonViTinh INTO @DonViTinh, @GiaTriQuiDoi
	WHILE @@FETCH_STATUS = 0
	BEGIN
		set @SoLuongQuiDoi = @Soluong / @GiaTriQuiDoi
		If @SoLuongQuiDoi > 0
		Begin
			set @SoLuongTonKho = @SoLuongTonKho + CONVERT(varchar(11),@SoLuongQuiDoi) + ' ' + @DonViTinh + ' '
			set @SoLuong = @SoLuong - (@SoLuongQuiDoi * @GiaTriQuiDoi)
		End
		FETCH NEXT FROM @curDanhSachDonViTinh INTO @DonViTinh, @GiaTriQuiDoi
	END
	
	Return @SoLuongTonKho
END
go

CREATE FUNCTION func_getMaNhaCungCapFromTenNhaCungCap
(
	-- Add the parameters for the function here
	@TenNhaCungCap nvarchar(10)
)
RETURNS varchar(10)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @MaNhaCungCap varchar(10)

	-- Add the T-SQL statements to compute the return value here
	SELECT @MaNhaCungCap = MaNhaCungCap from NhaCungCap where TenNhaCungCap = @TenNhaCungCap

	-- Return the result of the function
	RETURN @MaNhaCungCap

END
GO

CREATE FUNCTION func_getMaTaiKhoanFromTenTaiKhoan
(
	-- Add the parameters for the function here
	@tentaikhoan nvarchar(20)
)
RETURNS int
AS
BEGIN
	-- Declare the return variable here
	DECLARE @mataikhoan int

	-- Add the T-SQL statements to compute the return value here
	SELECT @mataikhoan = mataikhoan from taikhoan where tentaikhoan = @tentaikhoan

	-- Return the result of the function
	RETURN @mataikhoan

END
GO

